package com.geepi.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.geepi.entity.Page;


/**
 * 工具类
 * 
 * @author Administrator
 * 
 */
public class DbUtil {

	public static Connection conn = null;
	public static PreparedStatement ps = null;// 比statement执行效率高
	public static ResultSet rs = null;

	public static Connection getConn() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/geepi?useUnicode=true&characterEncoding=UTF-8";
			String user = "root";
			String pwd = "root";
			return DriverManager.getConnection(url, user, pwd);

		} catch (Exception e) {

			e.printStackTrace();
			return null;
		}

	}

	// String userId
	public static ResultSet getList(String userId, Page page) {
		String sql = "";
		int begin = (page.getCurrPage() - 1) * Page.PAGE_SIZE;
		if (userId == null) {
			// sql = "select * from myuser "; //不涉及分页查询的SQL语句
			sql = "select * from myuser limit " + begin + "," + Page.PAGE_SIZE;// 分页查询

		} else {

			sql = "select * from myuser where userId = " + userId;
		}

		try {

			conn = getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			return rs;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	public static void closeResource() {

		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	// 传参 表名
	public static int getTotal(String tableName) {
		String sql = "select count(1) total from " + tableName + ";";

		conn = getConn();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			int total = 0;
			while (rs.next()) {

				total = rs.getInt("total");
			}
			return total;
		} catch (SQLException e) {

			e.printStackTrace();

			return -1;
		} finally {
			closeResource();
		}

	}

	public static int edit(String sql) {
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);

			/*
			 * int m = ps.executeUpdate();
			 * System.out.println("-----dbutil-----"+m);
			 */
			return ps.executeUpdate(); // 运行成功，返回值为1

		} catch (SQLException e) {

			e.printStackTrace();
			return -1;
		} finally {

			closeResource();
		}

	}

	public static int executeUpdate(String sql) {
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			return ps.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();

			return -1;
		} finally {

			closeResource();
		}

	}

	/*
	 * public static void main(String[] args) { //DbUtil db = new DbUtil();
	 * String sql = "update myuser  set " +
	 * " userName='飞舞',age=22 ,password='000' " +" where userId = 25;"; int
	 * result = db.edit(sql); System.out.println("======"+result); ResultSet
	 * r=DbUtil.getList("4"); try { while (r.next()) {
	 * System.out.println(r.getObject(3)); } } catch (SQLException e) { // TODO
	 * Auto-generated catch block e.printStackTrace(); } }
	 */

}
